OpenEdge Data Management:<br />DataServer for Microsoft SQL Server Run Stored-Proc statement execution using the send-sql-statement option

The Progress 4GL also allows you to use stored-procedure syntax to send SQL statements and their native language extensions directly to a data source. The DataServer uses the RUN STORED–PROCEDURE statement with the send–sql–statement option to pass SQL statements to the data source. This option gives you access to Transact–SQL, providing you access to business logic for MS SQL Server. For example, you can issue Data Definition Language (DDL) statements from within Progress procedures. You can send multiple SQL statements by concatenating them and passing the string as a single parameter to the send–sql–statement option.

Like the option to define a RUN STORED-PROC statement either with or without the LOAD-RESULT-INTO options, you can define a send-sql-statement with or without the LOAD-RESULT-INTO option. The following section presents and briefly describes a sample use of each technique.

Without the Load-Result-Into option

You use the RUN STORED–PROC statement with the send–sql–statement option and pass the Transact-SQL statements as a parameter. The syntax of the statement must be valid Transact-SQL syntax. Example 3–1 shows how this code passes a SELECT statement as a parameter.

DEFINE VAR handle1 AS INTEGER. RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE ("SELECT name, address, city, state, postal_code FROM customer WHERE credit_limit >= 500"). FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1: DISPLAY proc-text-buffer. END. CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

DEFINE VAR handle1 AS INTEGER.
RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
 ("SELECT name, address, city, state, postal_code
 FROM customer WHERE credit_limit >= 500").
FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
	 DISPLAY proc-text-buffer.
END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

Example 3–1 returns the name, address, city, state, and postal_code for all customers whose max–credit is greater than or equal to $500. You must read the results into a buffer as you would with a stored procedure called by a Progress procedure. You can read the results into the proc–text–buffer defined by Progress as shown in the example above. Alternatively, you can define your own buffer from within your data source that can accept other data types as well as the CHARACTER data type.

With the Load-Result-Into option

Example 3–2 shows how to use the send-sql-statement with the LOAD-RESULT-INTO option. It also shows that the PROC-STATUS phrase must be defined as part of the RUN STORED-PROC statement because of the implicit CLOSE STORED-PROC that is associated with the LOAD-RESULT-INTO phrase.

DEFINE VARIABLE res AS INTEGER. DEFINE VARIABLE tthndl AS HANDLE. CREATE TEMP-TABLE tthndl. RUN STORED_PROCEDURE send-sql-statement LOAD-RESULT-INTO tthndl res=PROC-STATUS (“SELECT * FROM customer”). IF res THEN DISPLAY “succeeded”. DISPLAY stat.

DEFINE VARIABLE res AS INTEGER. 
DEFINE VARIABLE tthndl AS HANDLE. 
CREATE TEMP-TABLE tthndl. 
RUN STORED_PROCEDURE send-sql-statement LOAD-RESULT-INTO tthndl 
   res=PROC-STATUS (“SELECT * FROM customer”).
IF res THEN
   DISPLAY “succeeded”. 
DISPLAY stat.

Also note in Example 3–2 that the PROC-STATUS phrase does not need an associated PROC-HANDLE phrase to close the associated procedure because it is retrieved using the RUN STORED-PROC statement; although the PROC-HANDLE is typically used after the execution of the RUN STORED-PROC statement, it is not needed in this context because of the implicit procedure close.